clear
capture cd "INSERT PATH \STATA\input"

import excel popandgdp_3.xlsx, firstrow case(lower) clear

save "popandgdp_3.dta", replace

clear

use mergedirs

replace estatetaxcollections=estatetaxcollections*1000

egen panelid= group(state)
order state panelid year

gen usstate=1 if state~="Puerto Rico"
replace usstate=0 if usstate==.

sort state year


merge 1:1 state year using popandgdp_3

drop _merge  
     
sort state year

xtset panelid year

gen gdppercap= nomgdp/population
gen numhhs100150=numhhs*perc100150
gen numhhs150200=numhhs*perc150200
gen numhhs200more=numhhs*perc200more

gen numhhs100more=numhhs100150 + numhhs150200 + numhhs200more
gen numhhs150more=numhhs150200 + numhhs200more

drop perc*
gen pr=1 if state=="Puerto Rico"
replace pr=0 if state~="Puerto Rico" 


gen nomgdpbill=nomgdp/1000000000
gen popmill=population/1000000

bysort panelid: gen n =_n


capture cd "INSERT PATH \STATA\output"
gen estatetaxcollperret=estatetaxcollections/estatetaxfilings

gen estatetaxcollectionsmill=estatetaxcollections/1000000

gen filingspergdp=estatetaxfilings/nomgdpbill

gen prfilingsgdp=filingspergdp if state=="Puerto Rico"
bysort year: egen maxprfilingsgdp=max(prfilingsgdp) 
drop prfilingsgdp
rename maxprfilingsgdp prfilingsgdp 


bysort year: egen minfilingsgdp=min(filingspergdp) if pr==0
bysort year: egen filings25thgdp=pctile(filingspergdp) if pr==0, p(25)

bysort year: egen filingsmedgdp=pctile(filingspergdp) if pr==0, p(50)


bysort year: egen mincollspf=min(estatetaxcollperret) if pr==0
bysort year: egen collss25thpf=pctile(estatetaxcollperret) if pr==0, p(25)

bysort year: egen collsmedpf=pctile(estatetaxcollperret) if pr==0, p(50)

gen prcollspf=estatetaxcollperret if state=="Puerto Rico"
bysort year: egen maxprpf=max(prcollspf) 
drop prcollspf
rename maxprpf prcollspf 



sort state year

// Figure D1 
graph twoway line minfilingsgdp filings25thgdp prfilingsgdp year, sort

// Figure D2 
graph twoway line mincollspf collss25thpf prcollspf year, sort


//Table D1/////////////////////////////////////////////////
summ estatetaxcollectionsmill if pr==1, detail
summ estatetaxcollectionsmill if pr==0 , detail

summ estatetaxfilings if pr==1, detail
summ estatetaxfilings if pr==0, detail

summ numhhs200more if pr==1 , detail
summ numhhs200more if pr==0, detail

summ nomgdpbill if pr==1 , detail
summ nomgdpbill if pr==0, detail

summ popmill if pr==1 , detail
summ popmill if pr==0, detail

summ gdppercap if pr==1 , detail
summ gdppercap if pr==0, detail


//regressions///////////////////////////////////////////////////////////////////////////////////////

//Table 2/////////////////////////////////////////////////////////////////////////
xtreg estatetaxfilings  pr      i.year , robust
outreg2 using Table2.xls, append ctitle(estatetaxfilings 1) excel

xtreg estatetaxfilings nomgdpbill pr      i.year , robust
outreg2 using Table2.xls, append ctitle(estatetaxfilings 2) excel 

xtreg estatetaxfilings   popmill  pr   i.year , robust
outreg2 using Table2.xls, append ctitle(estatetaxfilings 3) excel 

xtreg estatetaxfilings numhhs200more  pr i.year , robust
outreg2 using Table2.xls, append ctitle(estatetaxfilings 4) excel 

xtreg estatetaxfilings nomgdpbill  popmill   numhhs200more  pr  i.year , robust
outreg2 using Table2.xls, append ctitle(estatetaxfilings 5) excel 
xttest0 // confirm random effects

//Table D2////////////////////////////////////////////////////////////////
gen estatetaxcollperretths=estatetaxcollperret/1000

xtreg estatetaxcollperretths  pr      i.year , robust
outreg2 using TableD2.xls, append ctitle(estatetaxcollperret 1) excel

xtreg estatetaxcollperretths nomgdpbill pr      i.year , robust
outreg2 using TableD2.xls, append ctitle(estatetaxcollperret 2) excel 

xtreg estatetaxcollperretths   popmill  pr   i.year , robust
outreg2 using TableD2.xls, append ctitle(estatetaxcollperret 3) excel 

xtreg estatetaxcollperretths numhhs200more  pr i.year , robust
outreg2 using TableD2.xls, append ctitle(estatetaxcollperret 4) excel 

xtreg estatetaxcollperretths nomgdpbill  popmill   numhhs200more  pr  i.year , robust
outreg2 using TableD2.xls, append ctitle(estatetaxcollperret 5) excel 
xttest0 // confirm random effects